CREATE TABLE [dbo].[tb_Familia]
(
[idFamilia] [int] NOT NULL IDENTITY(1, 1),
[Alumno] [int] NULL,
[Documento] [int] NULL,
[TipoDocumento] [int] NULL CONSTRAINT [DF_tb_Familia_TipoDocumento] DEFAULT ((1)),
[Apellido] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tb_Familia_Apellido] DEFAULT ('-'),
[Nombres] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Parentesco] [int] NULL,
[ACargo] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tb_Familia_ACargo] DEFAULT ('N'),
[EsTutor] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tb_Familia_EsTutor] DEFAULT ('S'),
[PatriaPotestad] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tb_Familia_PatriaPotestad] DEFAULT ('S'),
[Estudios] [int] NOT NULL CONSTRAINT [DF_tb_Familia_Estudios] DEFAULT ((6)),
[Ocupacion] [int] NOT NULL CONSTRAINT [DF_tb_Familia_Ocupacion] DEFAULT ((5)),
[Domicilio] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Telefono] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Vive] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tb_Familia_Vive] DEFAULT ('S'),
[FechaDeNacimiento] [datetime] NULL,
[Nacionalidad] [int] NULL,
[TelefonoDeUrgencia] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ViveConElAlumno] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Localidad] [int] NULL,
[CUIL] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EstadoCivil] [int] NULL,
[Sexo] [int] NULL,
[Observaciones] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cargo] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tb_Familia_Cargo] DEFAULT (suser_sname()),
[existe] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Familiar] [int] NULL,
[FechaRegistro] [datetime] NULL CONSTRAINT [DF_tb_Familia_FechaRegistro] DEFAULT (getdate())
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[cargoenfamiliar] ON [dbo].[tb_Familia] 
FOR INSERT
AS
SET NOCOUNT ON
update tb_familia set cargo = system_user where idfamilia = (select idfamilia from inserted)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[FamiliaresDuplicados] ON [dbo].[tb_Familia]
FOR INSERT
AS

SET NOCOUNT ON
DECLARE @documento INT

SELECT  @documento = Documento FROM Inserted 
update tb_familia set cargo = system_user where idfamilia = (select idfamilia from inserted)
if (SELECT count( *) FROM dbo.tb_familia WHERE @Documento = Documento) >1
 BEGIN
	ROLLBACK transaction 
	RAISERROR('El Familiar  ya ha sido ingresado solo ingrese la relacion con el alumno.', 16,1)
          
END
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[insertarfamiliar] ON [dbo].[tb_Familia] 
FOR INSERT
AS
SET NOCOUNT ON
declare @existe varchar(1)
declare @IDAgregado INT 

select @existe = existe from inserted

select @IDAgregado =  @@identity 


if @existe = 'S' begin
	insert into tb_relfamiliaralumno (alumno,familiar,parentezco)
	select alumno,familiar,parentesco from inserted
	delete from tb_familia where idfamilia = (select idfamilia from inserted)
end
if @existe <> 'S' OR @existe is null begin
insert into tb_relfamiliaralumno (alumno,familiar,parentezco)
	select alumno,@IDAgregado ,parentesco from inserted
end
GO
ALTER TABLE [dbo].[tb_Familia] ADD CONSTRAINT [PK_tb_Familia] PRIMARY KEY NONCLUSTERED  ([idFamilia]) WITH (FILLFACTOR=98) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Alumno] ON [dbo].[tb_Familia] ([Alumno]) WITH (FILLFACTOR=98) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Documento] ON [dbo].[tb_Familia] ([Documento]) WITH (FILLFACTOR=98) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_EstadosCiviles] FOREIGN KEY ([EstadoCivil]) REFERENCES [dbo].[tb_EstadosCiviles] ([idEstadoCivil])
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_NivelEstudios] FOREIGN KEY ([Estudios]) REFERENCES [dbo].[tb_NivelEstudios] ([idNivelEstudio])
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_Familia] FOREIGN KEY ([Familiar]) REFERENCES [dbo].[tb_Familia] ([idFamilia]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_Localidades] FOREIGN KEY ([Localidad]) REFERENCES [dbo].[tb_Localidades] ([idLocalidad])
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_Nacionalidad] FOREIGN KEY ([Nacionalidad]) REFERENCES [dbo].[tb_Nacionalidad] ([idNacionalidad])
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_Ocupaciones] FOREIGN KEY ([Ocupacion]) REFERENCES [dbo].[tb_Ocupaciones] ([idOcupacion])
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_Parentezcos] FOREIGN KEY ([Parentesco]) REFERENCES [dbo].[tb_Parentezcos] ([idParentezco])
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_Sexo] FOREIGN KEY ([Sexo]) REFERENCES [dbo].[tb_Sexo] ([idSexo])
GO
ALTER TABLE [dbo].[tb_Familia] WITH NOCHECK ADD CONSTRAINT [FK_tb_Familia_tb_TiposDeDocumento] FOREIGN KEY ([TipoDocumento]) REFERENCES [dbo].[tb_TiposDeDocumento] ([idTipoDocumento])
GO
ALTER TABLE [dbo].[tb_Familia] NOCHECK CONSTRAINT [FK_tb_Familia_tb_Familia]
GO
